package com.lyy.msgtool.util;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    /**  所有的下拉列表数据存在这个map中，key是对应的Excel列的序号，从0开始，value为下拉列表键对值 **/
    public static final  Map<Integer,Map<String,String>> ALL_SELECT_LIST_MAP = new HashMap<Integer,Map<String,String>> ();
        /**
         * 将Excel转换为对象集合
         * @param excel Excel 文件
         * @param rowNumber 从第几行开始读
         * @return
         */
        public static List<String> parseExcelToList(MultipartFile excel, Integer rowNumber){
            List<String> res = new ArrayList<String>();
            // 创建输入流，读取Excel
            InputStream is = null;
            Sheet sheet = null;
            Map<Integer,String> mapObj=new HashMap<>();
            try {
                is = excel.getInputStream();
                if (is != null) {
                    Workbook workbook = WorkbookFactory.create(is);
                    //默认只获取第一个工作表
                    sheet = workbook.getSheetAt(0);
                    if (sheet != null) {
                        int i = rowNumber;
                        Row row = sheet.getRow(i);
                        //第一行有多少数据
                        int firstRowCount=0;
                        while (row != null) {
                            //获取单元格数目
                            JSONObject obj=new JSONObject();
                            if(i==0) {
                                firstRowCount=row.getPhysicalNumberOfCells();
                                for (int j = 0; j <= firstRowCount; j++) {
                                    Cell cell = row.getCell(j);
                                    if (cell != null) {
                                        //设置单元格内容类型
                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                        //获取单元格值
                                        String value = cell.getStringCellValue() == null ? "" : cell.getStringCellValue();
                                        mapObj.put(j,value);
                                    }
                                }
                            }else{
                                for (int j = 0; j <= firstRowCount; j++) {
                                    Cell cell = row.getCell(j);
                                    if (cell != null) {
                                        //设置单元格内容类型
                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                        //获取单元格值
                                        String value = cell.getStringCellValue() == null ? null : cell.getStringCellValue();
                                        obj.put(mapObj.get(j),value);
                                    }
                                }
                                res.add(obj.toJSONString());
                            }
                            i++;
                            row=sheet.getRow(i);
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return res;
        }

}


